#region using_header 
using System;
using System.Text;
using System.Runtime.InteropServices;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
#endregion

namespace ManticLabs.TourIS.DAL
{

#region DAL_Types

	[Serializable]
	[TableMapping("Clients", 549576996)]
	public class DBClients : DalBase
	{

	    #region member_variables (Audax)

		private	ParameterCache	m_iParams;
		private	String	    m_address1;
		private	String	    m_address2;
		private	Int32	    m_clientid;
		private	String	    m_clientname;
		private	Int32	    m_compid;
		private	String	    m_contactno;
		private	String	    m_emailid;
		private	DateTime	m_expirydate;
		private	String	    m_homecountry;
		private	Boolean	    m_isactive;
		private	String	    m_nic;
		private	String	    m_passportno;
		private	String	    m_password;
		private	String	    m_reason;
		private	String	    m_remarks;

	#endregion

	    #region properties

		public String	Address1
		{
			get{return m_address1; }
			set{ m_address1 = value; }
		}
		public String	Address2
		{
			get{return m_address2; }
			set{ m_address2 = value; }
		}
		[PrimaryKeyColumn(true)]
		public Int32	Clientid
		{
			get{return m_clientid; }
			set{ m_clientid = value; }
		}
		public String	Clientname
		{
			get{return m_clientname; }
			set{ m_clientname = value; }
		}
		[PrimaryKeyColumn(true)]
		public Int32	Compid
		{
			get{return m_compid; }
			set{ m_compid = value; }
		}
		public String	Contactno
		{
			get{return m_contactno; }
			set{ m_contactno = value; }
		}
		public String	Emailid
		{
			get{return m_emailid; }
			set{ m_emailid = value; }
		}
		public DateTime	Expirydate
		{
			get{return m_expirydate; }
			set{ m_expirydate = value; }
		}
		public String	Homecountry
		{
			get{return m_homecountry; }
			set{ m_homecountry = value; }
		}
		public Boolean	Isactive
		{
			get{return m_isactive; }
			set{ m_isactive = value; }
		}
		public String	Nic
		{
			get{return m_nic; }
			set{ m_nic = value; }
		}
		public String	Passportno
		{
			get{return m_passportno; }
			set{ m_passportno = value; }
		}
		public String	Password
		{
			get{return m_password; }
			set{ m_password = value; }
		}
		public String	Reason
		{
			get{return m_reason; }
			set{ m_reason = value; }
		}
		public String	Remarks
		{
			get{return m_remarks; }
			set{ m_remarks = value; }
		}

	#endregion

	    #region methods (Audax)

		public	DBClients()
		{
			SetEmpty();
			CacheParameters();
		}

		static public DBClients[]	LoadAll()
		{
			ArrayList al = new ArrayList();
			string connStr = DalBase.ReadWebConnectionString("DBConnection");
			using(SqlConnection conn = new SqlConnection(connStr))
			{
				string cmdText =  "spr_LoadAllDBClientss";
				using(SqlCommand cmd = new SqlCommand(cmdText, conn))
				{
					cmd.CommandType = CommandType.StoredProcedure;
					SqlDataReader reader = null;
					try
					{
						conn.Open();
						reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
						while(reader.Read())
						{
							DBClients obj = new DBClients();
							obj.m_address1 = reader.IsDBNull(reader.GetOrdinal("Address1")) ? "" : reader.GetString(reader.GetOrdinal("Address1"));
							obj.m_address2 = reader.IsDBNull(reader.GetOrdinal("Address2")) ? "" : reader.GetString(reader.GetOrdinal("Address2"));
							obj.m_clientid = reader.IsDBNull(reader.GetOrdinal("ClientId")) ? -1 : reader.GetInt32(reader.GetOrdinal("ClientId"));
							obj.m_clientname = reader.IsDBNull(reader.GetOrdinal("ClientName")) ? "" : reader.GetString(reader.GetOrdinal("ClientName"));
							obj.m_compid = reader.IsDBNull(reader.GetOrdinal("CompId")) ? -1 : reader.GetInt32(reader.GetOrdinal("CompId"));
							obj.m_contactno = reader.IsDBNull(reader.GetOrdinal("ContactNo")) ? "" : reader.GetString(reader.GetOrdinal("ContactNo"));
							obj.m_emailid = reader.IsDBNull(reader.GetOrdinal("EmailId")) ? "" : reader.GetString(reader.GetOrdinal("EmailId"));
							obj.m_expirydate = reader.IsDBNull(reader.GetOrdinal("ExpiryDate")) ? "" : reader.GetValue(reader.GetOrdinal("ExpiryDate"));
							obj.m_homecountry = reader.IsDBNull(reader.GetOrdinal("HomeCountry")) ? "" : reader.GetString(reader.GetOrdinal("HomeCountry"));
							obj.m_isactive = reader.IsDBNull(reader.GetOrdinal("IsActive")) ? false : reader.GetBoolean(reader.GetOrdinal("IsActive"));
							obj.m_nic = reader.IsDBNull(reader.GetOrdinal("NIC")) ? "" : reader.GetString(reader.GetOrdinal("NIC"));
							obj.m_passportno = reader.IsDBNull(reader.GetOrdinal("PassportNo")) ? "" : reader.GetString(reader.GetOrdinal("PassportNo"));
							obj.m_password = reader.IsDBNull(reader.GetOrdinal("Password")) ? "" : reader.GetString(reader.GetOrdinal("Password"));
							obj.m_reason = reader.IsDBNull(reader.GetOrdinal("Reason")) ? "" : reader.GetString(reader.GetOrdinal("Reason"));
							obj.m_remarks = reader.IsDBNull(reader.GetOrdinal("Remarks")) ? "" : reader.GetString(reader.GetOrdinal("Remarks"));
							al.Add(obj);
						}
						reader.Close();
					}
					catch(Exception e)
					{
						if(null != reader)
						{
							reader.Close();
						}
						DalBase.LogException(e, false);
						throw;
					}
				}
			}
			return (DBClients[])al.ToArray(typeof(DBClients));
		}

		static public DBClients[]	Find(string sortCol, string[] columns, string[] values)
		{
			ArrayList al = new ArrayList();
			string connStr = DalBase.ReadWebConnectionString("DBConnection");
			using(SqlConnection conn = new SqlConnection(connStr))
			{
				string cmdText =  "spr_TourIS_Search";
				using(SqlCommand cmd = new SqlCommand(cmdText, conn))
				{
					cmd.CommandType = CommandType.StoredProcedure;
					// add the parameters
					cmd.Parameters.Add(new SqlParameter("@table", SqlDbType.VarChar)).Value = "Clients";
					if (null != sortCol && String.Empty != sortCol)
					{
						cmd.Parameters.Add(new SqlParameter("@sort_by_col", SqlDbType.VarChar)).Value = sortCol;
					}
					if (columns.Length > 0)
					{
						cmd.Parameters.Add(new SqlParameter("@col_1", SqlDbType.VarChar)).Value = columns[0];
						cmd.Parameters.Add(new SqlParameter("@val_1", SqlDbType.VarChar)).Value = values[0];
					}
					for (int j = 1; j < columns.Length; j++)
					{
						int nC = 1 + j;
						string paramC = "@col_" + nC.ToString();
						string paramV = "@val_" + nC.ToString();
						if (columns.Length > 0)
						{
							cmd.Parameters.Add(new SqlParameter(paramC, SqlDbType.VarChar)).Value = columns[j];
							cmd.Parameters.Add(new SqlParameter(paramV, SqlDbType.VarChar)).Value = values[j];
						}
					}
					SqlDataReader reader = null;
					try
					{
						conn.Open();
						reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
						while(reader.Read())
						{
							DBClients obj = new DBClients();
							obj.m_address1 = reader.IsDBNull(reader.GetOrdinal("Address1")) ? "" : reader.GetString(reader.GetOrdinal("Address1"));
							obj.m_address2 = reader.IsDBNull(reader.GetOrdinal("Address2")) ? "" : reader.GetString(reader.GetOrdinal("Address2"));
							obj.m_clientid = reader.IsDBNull(reader.GetOrdinal("ClientId")) ? -1 : reader.GetInt32(reader.GetOrdinal("ClientId"));
							obj.m_clientname = reader.IsDBNull(reader.GetOrdinal("ClientName")) ? "" : reader.GetString(reader.GetOrdinal("ClientName"));
							obj.m_compid = reader.IsDBNull(reader.GetOrdinal("CompId")) ? -1 : reader.GetInt32(reader.GetOrdinal("CompId"));
							obj.m_contactno = reader.IsDBNull(reader.GetOrdinal("ContactNo")) ? "" : reader.GetString(reader.GetOrdinal("ContactNo"));
							obj.m_emailid = reader.IsDBNull(reader.GetOrdinal("EmailId")) ? "" : reader.GetString(reader.GetOrdinal("EmailId"));
							obj.m_expirydate = reader.IsDBNull(reader.GetOrdinal("ExpiryDate")) ? "" : reader.GetValue(reader.GetOrdinal("ExpiryDate"));
							obj.m_homecountry = reader.IsDBNull(reader.GetOrdinal("HomeCountry")) ? "" : reader.GetString(reader.GetOrdinal("HomeCountry"));
							obj.m_isactive = reader.IsDBNull(reader.GetOrdinal("IsActive")) ? false : reader.GetBoolean(reader.GetOrdinal("IsActive"));
							obj.m_nic = reader.IsDBNull(reader.GetOrdinal("NIC")) ? "" : reader.GetString(reader.GetOrdinal("NIC"));
							obj.m_passportno = reader.IsDBNull(reader.GetOrdinal("PassportNo")) ? "" : reader.GetString(reader.GetOrdinal("PassportNo"));
							obj.m_password = reader.IsDBNull(reader.GetOrdinal("Password")) ? "" : reader.GetString(reader.GetOrdinal("Password"));
							obj.m_reason = reader.IsDBNull(reader.GetOrdinal("Reason")) ? "" : reader.GetString(reader.GetOrdinal("Reason"));
							obj.m_remarks = reader.IsDBNull(reader.GetOrdinal("Remarks")) ? "" : reader.GetString(reader.GetOrdinal("Remarks"));
							al.Add(obj);
						}
						reader.Close();
					}
					catch(Exception e)
					{
						if(null != reader)
						{
							reader.Close();
						}
						DalBase.LogException(e, false);
						throw;
					}
				}
			}
			return (DBClients[])al.ToArray(typeof(DBClients));
		}

		[PrimaryKeyColumn(true)]
		public void	LoadByPrimaryKey(Int32 clientid,Int32 compid)
		{
			string connStr = DalBase.ReadWebConnectionString("DBConnection");
			using(SqlConnection conn = new SqlConnection(connStr))
			{
				string cmdText =  "spr_LoadDBClientsByPK";
				using(SqlCommand cmd = new SqlCommand(cmdText, conn))
				{
					cmd.CommandType = CommandType.StoredProcedure;
					cmd.Parameters.Add(m_iParams["@clientid"]).Value = clientid;
					cmd.Parameters.Add(m_iParams["@compid"]).Value = compid;
					SqlDataReader reader = null;
					try
					{
						conn.Open();
						reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
						while(reader.Read())
						{
							this.m_address1 = reader.IsDBNull(reader.GetOrdinal("Address1")) ? "" : reader.GetString(reader.GetOrdinal("Address1"));
							this.m_address2 = reader.IsDBNull(reader.GetOrdinal("Address2")) ? "" : reader.GetString(reader.GetOrdinal("Address2"));
							this.m_clientid = reader.IsDBNull(reader.GetOrdinal("ClientId")) ? -1 : reader.GetInt32(reader.GetOrdinal("ClientId"));
							this.m_clientname = reader.IsDBNull(reader.GetOrdinal("ClientName")) ? "" : reader.GetString(reader.GetOrdinal("ClientName"));
							this.m_compid = reader.IsDBNull(reader.GetOrdinal("CompId")) ? -1 : reader.GetInt32(reader.GetOrdinal("CompId"));
							this.m_contactno = reader.IsDBNull(reader.GetOrdinal("ContactNo")) ? "" : reader.GetString(reader.GetOrdinal("ContactNo"));
							this.m_emailid = reader.IsDBNull(reader.GetOrdinal("EmailId")) ? "" : reader.GetString(reader.GetOrdinal("EmailId"));
							this.m_expirydate = reader.IsDBNull(reader.GetOrdinal("ExpiryDate")) ? "" : reader.GetValue(reader.GetOrdinal("ExpiryDate"));
							this.m_homecountry = reader.IsDBNull(reader.GetOrdinal("HomeCountry")) ? "" : reader.GetString(reader.GetOrdinal("HomeCountry"));
							this.m_isactive = reader.IsDBNull(reader.GetOrdinal("IsActive")) ? false : reader.GetBoolean(reader.GetOrdinal("IsActive"));
							this.m_nic = reader.IsDBNull(reader.GetOrdinal("NIC")) ? "" : reader.GetString(reader.GetOrdinal("NIC"));
							this.m_passportno = reader.IsDBNull(reader.GetOrdinal("PassportNo")) ? "" : reader.GetString(reader.GetOrdinal("PassportNo"));
							this.m_password = reader.IsDBNull(reader.GetOrdinal("Password")) ? "" : reader.GetString(reader.GetOrdinal("Password"));
							this.m_reason = reader.IsDBNull(reader.GetOrdinal("Reason")) ? "" : reader.GetString(reader.GetOrdinal("Reason"));
							this.m_remarks = reader.IsDBNull(reader.GetOrdinal("Remarks")) ? "" : reader.GetString(reader.GetOrdinal("Remarks"));
						}
						reader.Close();
					}
					catch(Exception e)
					{
						if(null != reader)
						{
							reader.Close();
						}
						DalBase.LogException(e, false);
						throw;
					}
				}
			}
		}

		[PrimaryKeyColumn(true)]
		public void 	Insert()
		{
			string connStr = DalBase.ReadWebConnectionString("DBConnection");
			using(SqlConnection conn = new SqlConnection(connStr))
			{
				string cmdText =  "spr_InsertDBClients";
				using(SqlCommand cmd = new SqlCommand(cmdText, conn))
				{
					cmd.CommandType = CommandType.StoredProcedure;
					cmd.Parameters.Add(m_iParams["@address1"]).Value = m_address1;
					cmd.Parameters.Add(m_iParams["@address2"]).Value = m_address2;
					cmd.Parameters.Add(m_iParams["@clientid"]).Value = m_clientid;
					cmd.Parameters.Add(m_iParams["@clientname"]).Value = m_clientname;
					cmd.Parameters.Add(m_iParams["@compid"]).Value = m_compid;
					cmd.Parameters.Add(m_iParams["@contactno"]).Value = m_contactno;
					cmd.Parameters.Add(m_iParams["@emailid"]).Value = m_emailid;
					cmd.Parameters.Add(m_iParams["@expirydate"]).Value = m_expirydate;
					cmd.Parameters.Add(m_iParams["@homecountry"]).Value = m_homecountry;
					cmd.Parameters.Add(m_iParams["@isactive"]).Value = m_isactive;
					cmd.Parameters.Add(m_iParams["@nic"]).Value = m_nic;
					cmd.Parameters.Add(m_iParams["@passportno"]).Value = m_passportno;
					cmd.Parameters.Add(m_iParams["@password"]).Value = m_password;
					cmd.Parameters.Add(m_iParams["@reason"]).Value = m_reason;
					cmd.Parameters.Add(m_iParams["@remarks"]).Value = m_remarks;
					try
					{
						conn.Open();
						cmd.ExecuteNonQuery();
					}
					catch(Exception e)
					{
						DalBase.LogException(e, false);
						throw;
					}
				}
			}
		}

		[PrimaryKeyColumn(true)]
		public Int32 	Update()
		{
			Int32 nRows = 0;
			string connStr = DalBase.ReadWebConnectionString("DBConnection");
			using(SqlConnection conn = new SqlConnection(connStr))
			{
				string cmdText = "spr_UpdateDBClientsByPK";
				using(SqlCommand cmd = new SqlCommand(cmdText, conn))
				{
					cmd.CommandType = CommandType.StoredProcedure;
					cmd.Parameters.Add(m_iParams["@address1"]).Value = m_address1;
					cmd.Parameters.Add(m_iParams["@address2"]).Value = m_address2;
					cmd.Parameters.Add(m_iParams["@clientid"]).Value = m_clientid;
					cmd.Parameters.Add(m_iParams["@clientname"]).Value = m_clientname;
					cmd.Parameters.Add(m_iParams["@compid"]).Value = m_compid;
					cmd.Parameters.Add(m_iParams["@contactno"]).Value = m_contactno;
					cmd.Parameters.Add(m_iParams["@emailid"]).Value = m_emailid;
					cmd.Parameters.Add(m_iParams["@expirydate"]).Value = m_expirydate;
					cmd.Parameters.Add(m_iParams["@homecountry"]).Value = m_homecountry;
					cmd.Parameters.Add(m_iParams["@isactive"]).Value = m_isactive;
					cmd.Parameters.Add(m_iParams["@nic"]).Value = m_nic;
					cmd.Parameters.Add(m_iParams["@passportno"]).Value = m_passportno;
					cmd.Parameters.Add(m_iParams["@password"]).Value = m_password;
					cmd.Parameters.Add(m_iParams["@reason"]).Value = m_reason;
					cmd.Parameters.Add(m_iParams["@remarks"]).Value = m_remarks;
					try
					{
						conn.Open();
						nRows = cmd.ExecuteNonQuery();
					}
					catch(Exception e)
					{
						DalBase.LogException(e, false);
						throw;
					}
				}
			}
			return nRows;
		}

		// WARNING
		// This special update allows updating primary keys ! Use it wisely
		// END WARNING
		[PrimaryKeyColumn(true)]
		public Int32 	UpdateEx(Int32 clientid,Int32 compid)
		{
			Int32 nRows = 0;
			string connStr = DalBase.ReadWebConnectionString("DBConnection");
			using(SqlConnection conn = new SqlConnection(connStr))
			{
				string cmdText = "spr_UpdateExDBClientsByPK";
				using(SqlCommand cmd = new SqlCommand(cmdText, conn))
				{
					cmd.CommandType = CommandType.StoredProcedure;
					cmd.Parameters.Add("@orig_clientid", SqlDbType.Int).Value = clientid;
					cmd.Parameters.Add("@orig_compid", SqlDbType.Int).Value = compid;
					cmd.Parameters.Add(m_iParams["@address1"]).Value = m_address1;
					cmd.Parameters.Add(m_iParams["@address2"]).Value = m_address2;
					cmd.Parameters.Add(m_iParams["@clientid"]).Value = m_clientid;
					cmd.Parameters.Add(m_iParams["@clientname"]).Value = m_clientname;
					cmd.Parameters.Add(m_iParams["@compid"]).Value = m_compid;
					cmd.Parameters.Add(m_iParams["@contactno"]).Value = m_contactno;
					cmd.Parameters.Add(m_iParams["@emailid"]).Value = m_emailid;
					cmd.Parameters.Add(m_iParams["@expirydate"]).Value = m_expirydate;
					cmd.Parameters.Add(m_iParams["@homecountry"]).Value = m_homecountry;
					cmd.Parameters.Add(m_iParams["@isactive"]).Value = m_isactive;
					cmd.Parameters.Add(m_iParams["@nic"]).Value = m_nic;
					cmd.Parameters.Add(m_iParams["@passportno"]).Value = m_passportno;
					cmd.Parameters.Add(m_iParams["@password"]).Value = m_password;
					cmd.Parameters.Add(m_iParams["@reason"]).Value = m_reason;
					cmd.Parameters.Add(m_iParams["@remarks"]).Value = m_remarks;
					try
					{
						conn.Open();
						nRows = cmd.ExecuteNonQuery();
					}
					catch(Exception e)
					{
						DalBase.LogException(e, false);
						throw;
					}
				}
			}
			return nRows;
		}

		[PrimaryKeyColumn(true)]
		public void 	Delete()
		{
			string connStr = DalBase.ReadWebConnectionString("DBConnection");
			using(SqlConnection conn = new SqlConnection(connStr))
			{
				string cmdText = "spr_DeleteDBClientsByPK";
				using(SqlCommand cmd = new SqlCommand(cmdText, conn))
				{
					cmd.CommandType = CommandType.StoredProcedure;
					cmd.Parameters.Add(m_iParams["@clientid"]).Value = m_clientid;
					cmd.Parameters.Add(m_iParams["@compid"]).Value = m_compid;
					try
					{
						conn.Open();
						cmd.ExecuteNonQuery();
						// reset the object
						SetEmpty();
					}
					catch(Exception e)
					{
						DalBase.LogException(e, false);
						throw;
					}
				}
			}
		}

		private void	SetEmpty()
		{
			m_address1 = "";
			m_address2 = "";
			m_clientid = -1;
			m_clientname = "";
			m_compid = -1;
			m_contactno = "";
			m_emailid = "";
			m_expirydate = "";
			m_homecountry = "";
			m_isactive = false;
			m_nic = "";
			m_passportno = "";
			m_password = "";
			m_reason = "";
			m_remarks = "";
		}

		private void	CacheParameters()
		{
			m_iParams = new ParameterCache();
			m_iParams.Add(new SqlParameter("@address1",SqlDbType.VarChar));
			m_iParams.Add(new SqlParameter("@address2",SqlDbType.VarChar));
			m_iParams.Add(new SqlParameter("@clientid",SqlDbType.Int));
			m_iParams.Add(new SqlParameter("@clientname",SqlDbType.VarChar));
			m_iParams.Add(new SqlParameter("@compid",SqlDbType.Int));
			m_iParams.Add(new SqlParameter("@contactno",SqlDbType.VarChar));
			m_iParams.Add(new SqlParameter("@emailid",SqlDbType.VarChar));
			m_iParams.Add(new SqlParameter("@expirydate",SqlDbType.Variant));
			m_iParams.Add(new SqlParameter("@homecountry",SqlDbType.VarChar));
			m_iParams.Add(new SqlParameter("@isactive",SqlDbType.Bit));
			m_iParams.Add(new SqlParameter("@nic",SqlDbType.VarChar));
			m_iParams.Add(new SqlParameter("@passportno",SqlDbType.VarChar));
			m_iParams.Add(new SqlParameter("@password",SqlDbType.VarChar));
			m_iParams.Add(new SqlParameter("@reason",SqlDbType.VarChar));
			m_iParams.Add(new SqlParameter("@remarks",SqlDbType.VarChar));
		}


	#endregion

	}

#endregion //DAL_Types
}

